package myta.bean;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import myta.model.DataRow;
import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;
import myta.service.Utility;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class AnalisaSiswa {
	
	private DataTable datatable;
	
	public AnalisaSiswa() {
		try {
			SessionSiswa s = (SessionSiswa)Utility.getBean("sessionSiswa");
			loadDatatable(s.getNis());
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	private void loadDatatable(String nis) throws Exception{
		datatable = new DataTable();
		
		String sql = "select thnajaran, namakelas, nis, nama, namamapel, cast((kd + uts + (2 * uas)) / 4  as signed) as na from ( ";
		sql += "select ";
		sql += "thnajaran, namakelas, nis, nama, namamapel, tugas, ulangan_harian, cast((tugas + ulangan_harian) / 2 as signed) as kd , uts, uas ";
		sql += "from ( ";
		
		sql += "select ";
		sql += "sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel, sum(if(jn.jenisnilai='Tugas',nd.nilai,0)) as tugas, ";
		sql += "sum(if(jn.jenisnilai='Ulangan Harian',nd.nilai,0)) as ulangan_harian,  ";
		sql += "sum(if(jn.jenisnilai='UTS',nd.nilai,0)) as uts, sum(if(jn.jenisnilai='UAS',nd.nilai,0)) as uas ";
		sql += "from nilai n ";
		sql += "inner join nilai_detail nd on n.idnilai = nd.idnilai ";
		sql += "inner join jenisnilai jn on n.idjenisnilai = jn.idjenisnilai ";
		sql += "inner join mapel m on n.idmapel = m.idmapel ";
		sql += "inner join siswa s on s.nis = nd.nis ";
		sql += "inner join kelassiswa ks on n.idkelassiswa = ks.idkelassiswa ";
		sql += "inner join kelas k on ks.idkelas = k.idkelas ";
		sql += "inner join semester sm on k.idsemester = sm.idsemester ";
		sql += "where nd.nis like ? ";
		sql += "group by sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel ";
		sql += "order by sm.thnajaran, k.namakelas, s.nama, m.namamapel ";
		
		sql += ") as datanilai ";
		sql += ") as datanilaisiswa;";
		
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, nis.equalsIgnoreCase("-")?"%%":"%"+nis+"%");
		datatable = command.executeQuery();
		
		Session s = (Session)Utility.getBean("sessionBean");
		
		if(s.getListAnalis().size() > 0){
			s.getListAnalis().remove(0);
		}
		
		DataAnalisa dn = new DataAnalisa();
		dn.setNis(datatable.getRow(0).getString("nis"));
		dn.setNama(datatable.getRow(0).getString("nama"));
		List<DataDetailAnalisa> listdatadetail = new ArrayList<DataDetailAnalisa>();
		
		for(int i=0;i<datatable.getRowCount();i++){
			DataRow dr = datatable.getRow(i);
			String thnajaran = dr.getString("thnajaran");
			String namamapel = dr.getString("namamapel");
			Integer na = dr.getInteger("na");
			
			listdatadetail.add(new DataDetailAnalisa(thnajaran, namamapel, na));
		}
		
		dn.setListOfNilai(listdatadetail);
		s.getListAnalis().add(dn);
	}
	
	public void proses(){
		try {
			SessionSiswa s = (SessionSiswa)Utility.getBean("sessionSiswa");
			loadDatatable(s.getNis());
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	public String pdf(){
		try {
			Session s = (Session)Utility.getBean("sessionBean");
			JRBeanCollectionDataSource jrbean = new JRBeanCollectionDataSource(s.getListAnalis());
			String reportpath = Utility.getReportsPath() + "analisa.jasper";
			JasperPrint jp = JasperFillManager.fillReport(reportpath, new HashMap<String, Object>(), jrbean);
			HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
			httpServletResponse.addHeader("Content-disposition", "attachment; filename=analisa.pdf");
			ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
			JasperExportManager.exportReportToPdfStream(jp, servletOutputStream);
			FacesContext.getCurrentInstance().responseComplete();
			return null;
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
			return "#";
		}
	}

	public DataTable getDatatable() {
		return datatable;
	}

	public void setDatatable(DataTable datatable) {
		this.datatable = datatable;
	}
}
